﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using BIS.Portal.Modules.CMS.Entity;
using BIS.Utility.DAL;
using System.Data.SqlClient;
using BIS.Utility.DAL.DataMapper;

namespace BIS.Portal.Modules.CMS.DAL
{
    public class AdminCmsArticleDAL
    {

        /// <summary>
        /// Gets the by language in use id.
        /// </summary>
        /// <param name="languageInUseId">The language in use id.</param>
        /// <returns></returns>
        //public static IList<AdminCmsArticleEntity> GetByLanguageInUseId(int languageInUseId)
        //{
        //    IList<AdminCmsArticleEntity> list;

        //    using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
        //                                CommandType.StoredProcedure,
        //                                "BIS_sCMS_ARTICLE_GetByLanguageUser",
        //                                new SqlParameter("@language_in_use_id", languageInUseId)))
        //    {
        //        list = EntityMapper.FillCollection<AdminCmsArticleEntity>(reader);
        //    }

        //    return list;
        //}

        /// <summary>
        /// Gets the by article in use id.
        /// </summary>
        /// <param name="articleId">The article id.</param>
        /// <returns></returns>
        public static AdminCmsArticleEntity GetByArticleId(int articleId)
        {
            AdminCmsArticleEntity entity = new AdminCmsArticleEntity();

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                                                CommandType.StoredProcedure,
                                                                "BIS_sCMS_ARTICLE_GetByArticleId",
                                                                new SqlParameter("@article_id", articleId)))
            {
                entity = EntityMapper.FillObject<AdminCmsArticleEntity>(reader);
            }

            return entity;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="articleId"></param>
        /// <returns></returns>
        public static AdminCmsArticleIndependentEntity GetByArticleIndependentId(int articleId)
        {
            AdminCmsArticleIndependentEntity entity = new AdminCmsArticleIndependentEntity();

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                                                CommandType.StoredProcedure,
                                                                "BIS_sCMS_ARTICLE_GetByArticleIndependentId",
                                                                new SqlParameter("@article_id", articleId)))
            {
                entity = EntityMapper.FillObject<AdminCmsArticleIndependentEntity>(reader);
            }

            return entity;
        }
        public static void GetLastArticleId(ref int last_articleId)
        {
            SqlParameter param = new SqlParameter("@last_articleId", SqlDbType.Int)
                                     {
                                         Direction = ParameterDirection.Output
                                     };
          SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                      CommandType.StoredProcedure,
                                      "BIS_sCMS_ARTICLE_MEDIA_GetLastArticleId",
                                      param);

             last_articleId=(int) param.Value;
        }
        /// <summary>
        /// Gets Category name.
        /// </summary>
        /// <summary>
        /// Searches the article.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <param name="keyword">The keyword.</param>
        /// <param name="isHotArticle">The news hot</param>
        /// <returns></returns>
        public static IList<AdminCmsArticleEntity> SearchArticle(string keyword, int categoryId, int isHotArticle, int status, int languageInUseId, DateTime? postDateStart, DateTime? postDateEnd)
        {
            IList<AdminCmsArticleEntity> list;

            SqlParameter[] param = new SqlParameter[]
                                       {                                                                                      
                                           new SqlParameter("@keyword", keyword),
                                           new SqlParameter("@category_id", categoryId),
                                           new SqlParameter("@is_hot_article", isHotArticle),
                                           new SqlParameter("@status", status), 
                                           new SqlParameter("@language_in_use_id",languageInUseId), 
                                           new SqlParameter("@post_date_start", postDateStart),
                                           new SqlParameter("@post_date_end", postDateEnd)                                           
                                       };

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_SearchArticle",
                                        param))
            {
                list = EntityMapper.FillCollection<AdminCmsArticleEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="categoryId"></param>
        /// <param name="isHotArticle"></param>
        /// <param name="status"></param>
        /// <param name="languageInUseId"></param>
        /// <param name="postDateStart"></param>
        /// <param name="postDateEnd"></param>
        /// <returns></returns>
        public static IList<AdminCmsArticleEntity> SearchArticleNotSpecialDisplay(string keyword, int categoryId, int isHotArticle, int status, int languageInUseId, DateTime? postDateStart, DateTime? postDateEnd)
        {
            IList<AdminCmsArticleEntity> list;

            SqlParameter[] param = new SqlParameter[]
                                       {                                                                                      
                                           new SqlParameter("@keyword", keyword),
                                           new SqlParameter("@category_id", categoryId),
                                           new SqlParameter("@is_hot_article", isHotArticle),
                                           new SqlParameter("@status", status), 
                                           new SqlParameter("@language_in_use_id",languageInUseId), 
                                           new SqlParameter("@post_date_start", postDateStart),
                                           new SqlParameter("@post_date_end", postDateEnd)                                           
                                       };

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_SearchArticleNotSpecialDisplay",
                                        param))
            {
                list = EntityMapper.FillCollection<AdminCmsArticleEntity>(reader);
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="status"></param>
        /// <param name="languageInUseId"></param>
        /// <param name="postDateStart"></param>
        /// <param name="postDateEnd"></param>
        /// <returns></returns>
        public static IList<AdminCmsArticleIndependentEntity> SearchArticleIndependent(string keyword, int status, int languageInUseId, DateTime? postDateStart, DateTime? postDateEnd)
        {
            IList<AdminCmsArticleIndependentEntity> list;

            SqlParameter[] param = new SqlParameter[]
                                       {                                                                                      
                                           new SqlParameter("@keyword", keyword),                                           
                                           new SqlParameter("@status", status), 
                                           new SqlParameter("@language_in_use_id",languageInUseId), 
                                           new SqlParameter("@post_date_start", postDateStart),
                                           new SqlParameter("@post_date_end", postDateEnd)                                           
                                       };

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_SearchArticleIndependent",
                                        param))
            {
                list = EntityMapper.FillCollection<AdminCmsArticleIndependentEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// Deletes.
        /// </summary>
        /// <param name="articleId">The article id.</param>
        /// <returns></returns>
        public static int DeleteArticle(int articleId)
        {
            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                             CommandType.StoredProcedure,
                                             "BIS_sCMS_DELETEARTICLE",
                                             new SqlParameter("@article_id", articleId));
        }     

        /// <summary>
        /// Adds the Article.
        /// </summary>
        /// <param name="cmsArticleEntity">The CMS article entity.</param>
        /// <returns></returns>
        public static int AddArticle(AdminCmsArticleEntity cmsArticleEntity)
        {
            SqlParameter paraArticleId = new SqlParameter("@article_id", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };
            SqlParameter[] param = new SqlParameter[]
                                       {
                                           paraArticleId,
                                           new SqlParameter("@page_alias", cmsArticleEntity.Page_alias),                                         
                                           new SqlParameter("@thumb_image",cmsArticleEntity.Thumb_image),
                                           new SqlParameter("@full_image", cmsArticleEntity.Full_image),
                                           new SqlParameter("@language_in_use_id", cmsArticleEntity.Language_in_use_id),
                                           new SqlParameter("@article_title", cmsArticleEntity.Article_title),
                                           new SqlParameter("@article_description", cmsArticleEntity.Article_description),
                                           new SqlParameter("@article_content", cmsArticleEntity.Article_content),
                                           new SqlParameter("@article_meta_keyword", cmsArticleEntity.Article_meta_keyword),
                                           new SqlParameter("@post_date", cmsArticleEntity.Post_date),
                                           new SqlParameter("@is_hot_article", cmsArticleEntity.Is_hot_article),
                                           new SqlParameter("@view_count", cmsArticleEntity.View_count),
                                           new SqlParameter("@last_update", cmsArticleEntity.Last_date),
                                           new SqlParameter("@last_update_by", cmsArticleEntity.Last_update_by),
                                           new SqlParameter("@status", cmsArticleEntity.Status)                                                                                                                                                                                                                                                                                                                                                                                                                         
                                       };

            if (cmsArticleEntity.Page_alias == "")
            {
                param[1].Value = DBNull.Value;
            }
            int i = SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                              CommandType.StoredProcedure,
                                              "BIS_sCMS_ARTICLE_AddArticle",
                                              param);
            cmsArticleEntity.Article_id = 0;
            int articleId = 0;
            if (i > 0 && paraArticleId.Value != null)
            {
                int.TryParse(paraArticleId.Value.ToString(), out articleId);
                cmsArticleEntity.Article_id = articleId;
            }

            return i;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmsArticleEntity"></param>
        /// <returns></returns>
        public static int AddArticleIndependent(AdminCmsArticleIndependentEntity cmsArticleEntity)
        {
            SqlParameter paraArticleId = new SqlParameter("@article_id", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };
            SqlParameter[] param = new SqlParameter[]
                                       {
                                           paraArticleId,
                                           new SqlParameter("@page_alias", cmsArticleEntity.Page_alias),                                         
                                           new SqlParameter("@thumb_image",cmsArticleEntity.Thumb_image),
                                           new SqlParameter("@full_image", cmsArticleEntity.Full_image),
                                           new SqlParameter("@language_in_use_id", cmsArticleEntity.Language_in_use_id),
                                           new SqlParameter("@article_title", cmsArticleEntity.Article_title),
                                           new SqlParameter("@article_description", cmsArticleEntity.Article_description),
                                           new SqlParameter("@article_content", cmsArticleEntity.Article_content),
                                           new SqlParameter("@article_meta_keyword", cmsArticleEntity.Article_meta_keyword),
                                           new SqlParameter("@post_date", cmsArticleEntity.Post_date),
                                           new SqlParameter("@is_hot_article", cmsArticleEntity.Is_hot_article),
                                           new SqlParameter("@view_count", cmsArticleEntity.View_count),
                                           new SqlParameter("@last_update", cmsArticleEntity.Last_date),
                                           new SqlParameter("@last_update_by", cmsArticleEntity.Last_update_by),
                                           new SqlParameter("@status", cmsArticleEntity.Status)                                                                                                                                                                                                                                                                                                                                                                                                                         
                                       };

            if (cmsArticleEntity.Page_alias == "")
            {
                param[1].Value = DBNull.Value;
            }
            int i = SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                              CommandType.StoredProcedure,
                                              "BIS_sCMS_ARTICLE_AddArticle",
                                              param);
            cmsArticleEntity.Article_id = 0;
            int articleId = 0;
            if (i > 0 && paraArticleId.Value != null)
            {
                int.TryParse(paraArticleId.Value.ToString(), out articleId);
                cmsArticleEntity.Article_id = articleId;
            }

            return i;
        }
        /// <summary>
        /// Updates the article.
        /// </summary>
        /// <param name="cmsArticleEntity">The CMS article entity.</param>
        /// <returns></returns>
        public static int UpdateArticle(AdminCmsArticleEntity entity)
        {
            SqlParameter[] param = new SqlParameter[]
                                       {                   
                                           new SqlParameter("@article_id", entity.Article_id),   
                                           new SqlParameter("@page_alias",entity.Page_alias),                                           
                                           new SqlParameter("@thumb_image",entity.Thumb_image),
                                           new SqlParameter("@full_image",entity.Full_image),
                                           new SqlParameter("@language_in_use_id",entity.Language_in_use_id),
                                           new SqlParameter("@article_title", entity.Article_title),
                                           new SqlParameter("@article_description", entity.Article_description),
                                           new SqlParameter("@article_content", entity.Article_content),
                                           new SqlParameter("@article_meta_keyword", entity.Article_meta_keyword),
                                           new SqlParameter("@post_date", entity.Post_date),
                                           new SqlParameter("@is_hot_article", entity.Is_hot_article),
                                           new SqlParameter("@last_update", entity.Last_date),
                                           new SqlParameter("@last_update_by", entity.Last_update_by),
                                           new SqlParameter("@status", entity.Status)
                                           
                                       };

            if (entity.Page_alias == "")
            {
                param[1].Value = DBNull.Value;

            }

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                             CommandType.StoredProcedure,
                                             "BIS_sCMS_ARTICLE_UpdateArticle",
                                             param);
        }
        /// <summary>
        /// Updates the status.
        /// </summary>
        /// <param name="articleId">The article id.</param>
        /// <param name="status">The status.</param>
        /// <param name="isHotArticle"></param>
        /// <returns></returns>
        public static int UpdateStatusInPage(int articleId, int status, int isHotArticle)
        {

            SqlParameter[] sqlParas = new SqlParameter[]
                                         {
                                             new SqlParameter("@articleId",articleId),                                            
                                             new SqlParameter("@status",status),                                            
                                             new SqlParameter("@isHotArticle",isHotArticle)                                             
                                         };

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                               CommandType.StoredProcedure,
                                               "BIS_sCMS_ARTICLE_UpdateStatusInPage",
                                               sqlParas);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="articleId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static int UpdateStatusInPage2(int articleId, int status)
        {

            SqlParameter[] sqlParas = new SqlParameter[]
                                         {
                                             new SqlParameter("@articleId",articleId),                                            
                                             new SqlParameter("@status",status)                                                                                                                                     
                                         };

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                               CommandType.StoredProcedure,
                                               "BIS_sCMS_ARTICLE_UpdateStatusInPage2",
                                               sqlParas);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="iArticle_id"></param>
        /// <param name="iCheck_count"></param>
        public static void CheckArticleId(int iArticle_id,ref int iCheck_count)
        {
            SqlParameter[] sqlParas = new SqlParameter[]
                                          {
                                              new SqlParameter("@article_id", iArticle_id),
                                              new SqlParameter("@check_count", SqlDbType.Int)
                                                  {
                                                      Direction = ParameterDirection.Output
                                                 }                                                                                        
                                         };
            SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                                         CommandType.StoredProcedure,
                                                         "BIS_sCMS_ARTICLE_MEDIA_CheckArticleId",
                                                         sqlParas);


            iCheck_count = (int) sqlParas[1].Value;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int UpdateArticleIndependent(AdminCmsArticleIndependentEntity entity)
        {
            SqlParameter[] param = new SqlParameter[]
                                       {                   
                                           new SqlParameter("@article_id", entity.Article_id),   
                                           new SqlParameter("@page_alias",entity.Page_alias),                                           
                                           new SqlParameter("@thumb_image",entity.Thumb_image),
                                           new SqlParameter("@full_image",entity.Full_image),
                                           new SqlParameter("@language_in_use_id",entity.Language_in_use_id),
                                           new SqlParameter("@article_title", entity.Article_title),
                                           new SqlParameter("@article_description", entity.Article_description),
                                           new SqlParameter("@article_content", entity.Article_content),
                                           new SqlParameter("@article_meta_keyword", entity.Article_meta_keyword),
                                           new SqlParameter("@post_date", entity.Post_date),
                                           new SqlParameter("@is_hot_article", entity.Is_hot_article),
                                           new SqlParameter("@last_update", entity.Last_date),
                                           new SqlParameter("@last_update_by", entity.Last_update_by),
                                           new SqlParameter("@status", entity.Status)
                                           
                                       };

            if (entity.Page_alias == "")
            {
                param[1].Value = DBNull.Value;

            }

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                             CommandType.StoredProcedure,
                                             "BIS_sCMS_ARTICLE_UpdateArticle",
                                             param);
        }
    }
}
